<template>
  <h1>flutter webview sqflite api</h1>
  <el-button type="primary" @click="handleCreateTable">
    创建临时表 temp_doc
  </el-button>
  <el-button type="primary" @click="handleDropTable"> 删除临时表 </el-button>
  <el-button type="primary" @click="handleInsertSql"> 插入数据 </el-button>
  <el-button type="primary" @click="handleDelSql"> 删除数据 </el-button>
  <el-button type="primary" @click="handleQuery1Sql"> 查询表1数据 </el-button>
  <el-button type="primary" @click="handleQuery2Sql"> 查询表2数据 </el-button>
  <el-button type="primary" @click="handleTransactionSql"> 事务测试 </el-button>
  <el-button type="primary" @click="handleBatchExecuteSql">
    批量执行sql
  </el-button>
  <el-button type="primary" @click="handleQuerySqlByPage"> 分页查询 </el-button>
  <el-col :span="16">
    <p>日志：<span v-html="logs"></span></p>
  </el-col>
</template>

<script setup lang="ts">
import { ref, onMounted } from "vue";
import { ElMessage, ElMessageBox } from "element-plus";
import handlerFile from "./handlerFile";
import handlerDb from "./handlerDb";
import utils from "../../../utils/utils";

const logs = ref("");
const orderIndex = ref(0);

const handleCreateTable = async () => {
  let sql1 = `
CREATE TABLE "temp_doc"(
 [documentId] varchar(64) PRIMARY KEY
,[title] varchar(255)
,[documentYear] int
,[createUser] varchar(64)
,[createTime] datetime
); ";
`;

  let sql2 = `
CREATE TABLE "temp2_doc"(
 [documentId] varchar(64) PRIMARY KEY
,[title] varchar(255)
,[documentYear] int
,[createUser] varchar(64)
,[createTime] datetime
); ";
`;

  let initSql = `
  insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (1,'标题1',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (2,'标题2',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (3,'标题3',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (4,'标题4',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (5,'标题5',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (6,'标题6',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (7,'标题7',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (8,'标题9',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (9,'标题9',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (10,'标题10',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (11,'标题11',2023,'admin','2023-05-18 15:47:00');
insert into temp_doc(documentId, title, documentYear, createUser, createTime) values (12,'标题12',2023,'admin','2023-05-18 15:47:00');
`;
  let params = [];
  try {
    await handlerDb.execute(sql1, params);
    await handlerDb.execute(sql2, params);
    loginfo(`操作成功`);
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleDropTable = async () => {
  let sql = "drop table temp_doc; drop table temp2_doc;";
  let params = [];
  try {
    await handlerDb.execute(sql, params);
    loginfo(`操作成功`);
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleInsertSql = async () => {
  let sql1 =
    "insert into temp_doc(documentId, title, documentYear, createUser) values (?,?,?,?)";
  let sql2 =
    "insert into temp2_doc(documentId, title, documentYear, createUser) values (?,?,?,?)";
  let params1 = [
    utils.getUUID(),
    `表1测试标题${orderIndex.value}`,
    2023,
    "admin",
  ];
  let params2 = [
    utils.getUUID(),
    `表2测试标题${orderIndex.value}`,
    2023,
    "admin",
  ];
  orderIndex.value++;
  try {
    let data = await handlerDb.insert(sql1, params1);
    loginfo("handleInsertSql:" + JSON.stringify(data));
    data = await handlerDb.insert(sql2, params2);
    loginfo("handleInsertSql:" + JSON.stringify(data));
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleDelSql = async () => {
  let sql1 = "delete from temp_doc where createUser=?";
  let sql2 = "delete from temp2_doc where createUser=?";
  let params = ["admin"];
  try {
    let cnt = await handlerDb.del(sql1, params);
    loginfo("handleDelSql:" + cnt);
    cnt = await handlerDb.del(sql2, params);
    loginfo("handleDelSql:" + cnt);
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleQuery1Sql = async () => {
  let sql = "select * from temp_doc ";
  let params = [];
  try {
    let data = await handlerDb.query(sql, params);
    loginfo("handleQuerySql:" + JSON.stringify(data));
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleQuery2Sql = async () => {
  let sql = "select * from temp2_doc ";
  let params = [];
  try {
    let data = await handlerDb.query(sql, params);
    loginfo("handleQuerySql:" + JSON.stringify(data));
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleBatchExecuteSql = async () => {
  let sql1 = "update temp_doc set title=? where createUser=?";
  let sql2 = "update temp2_doc set title=? where documentYear=?";
  let list = [];
  list.push([sql1, "表1标题", "admin"]);
  list.push([sql2, "表2标题", 2023]);
  try {
    await handlerDb.batchExecute(list);
    loginfo(`操作成功`);
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleTransactionSql = async () => {
  let sql1 =
    "insert into temp_doc(documentId, title, documentYear, createUser) values (?,?,?,?)";
  // 测试，sql2 插入一个不存在的字段 title2
  let sql2 =
    "insert into temp2_doc(documentId, title, documentYear, createUser) values (?,?,?,?)";
  let params1 = [
    utils.getUUID(),
    `表一测试标题${orderIndex.value}`,
    2023,
    "admin",
  ];
  let params2 = [
    utils.getUUID(),
    `表二测试标题${orderIndex.value}`,
    2023,
    "admin",
  ];
  orderIndex.value++;
  let list = [];
  list.push([sql1, ...params1]);
  list.push([sql2, ...params2]);
  try {
    await handlerDb.transaction(list);
    loginfo("handleTransactionSql, ok");
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const handleQuerySqlByPage = async () => {
  let sql = "select * from temp_doc ";
  let params = [];
  try {
    let data = await handlerDb.query(sql, params);
    loginfo("handleQuerySql:" + JSON.stringify(data));
  } catch (e) {
    loginfo(`execute 异常：` + e);
  }
};

const loginfo = (msg: string) => {
  logs.value = msg + "<br>";
};

onMounted(() => {
  loginfo("onMounted");
});
</script>
